package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.kit.PoiExt;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.CrmDemand;
import com.zzmcc.jms.model.Demand;
import com.zzmcc.jms.model.Project;
import util.ChristStringUtil;
import util.DateUtil;
import util.ModelUtils;
import util.StringUtil;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Crm_Demand;

public class ReportController extends BaseController {
    private static final Logger log =  Logger.getLogger(ReportController.class);
 	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        String name = getPara("demand.name");
		String cusName = getPara("demand.cusName");
		String memo = getPara("demand.memo");
		String statusName = getPara("demand.statusName");
		String code = getPara("demand.code");
		String isSelect = getPara("demand.isSelect");
		String isCrm = getPara("demand.isCrm");
		String crmid = getPara("demand.crmid");


		String demandOwner = getPara("demand.demandOwner");
        String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String homeCounty = getPara("demand.homeCounty");

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }

		if(ChristStringUtil.isNotEmpty(crmid)){
			whee.append(" and crmid like ?");
			param.add("%" + crmid + "%");
		}


		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(statusName)){
			whee.append(" and statusName like ?");
			param.add("%" + statusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add(demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty LIKE ?");
			param.add("%" + homeCounty + "%");
		}





		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(createDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(createDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		Page<CrmDemand> pager = CrmDemand.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
                "select a.*,b.requestStatus,b.rentDate,b.modifyDate \n",
                " from crm_demand a LEFT JOIN bas_demand b on a.crmid=b.crmid \n" +
				"where 1=1 "+ whee.toString(),
                param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("cusName",cusName);
		setAttr("memo",memo);
		setAttr("statusName",statusName);
		setAttr("code",code);
		setAttr("demandOwner",demandOwner);
		setAttr("isSelect",isSelect);
		setAttr("isCrm",isCrm);
		setAttr("crmid",crmid);


		setAttr("beginDate",beginDate);
		setAttr("endDate",endDate);
		setAttr("homeCounty",homeCounty);
		render("../report_list.html");

	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		render("../demand_input.html");
	}

	public void save() {
		Demand m = getModel(Demand.class, "demand");
		ModelUtils.fillForSave(m);
		if(StringUtil.isEmpty(m.getStr("did"))){
			ModelUtils.fillUUID(m, "did");
		}
		if(StringUtil.isNotEmpty(m.getStr("crmid"))){
			m.set("isCrm","是");
		}
		m.save();
		renderDWZSuccessJson("添加成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void edit() {
		Demand demand = Demand.dao.findById(getPara("id"));
		setAttr("demand", demand);
		render("../demand_input.html");
	}

	public void update() {
		Demand m = getModel(Demand.class, "demand");
		if(StringUtil.isEmpty(m.getStr("did"))){
			ModelUtils.fillUUID(m,"did");
		}
		if(StringUtil.isNotEmpty( m.getStr("crmid"))){
			m.set("isCrm","是");
		}
		ModelUtils.fillForUpdate(m);
		m.update();
		renderDWZSuccessJson("修改成功！");
	}

	

	public void view() {
		setAttr("project",Project.dao.findById(getPara("id")));
		render("../demand_detail.html");
	}


	public void imp() {
		render("../demand_input_imp.html");
	}

	public void impData() {
		String path = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
		UploadFile file = getFile();
		String fileName = null;
		if (file !=null) {
			File source = file.getFile();
			fileName = file.getFileName();
			try {

				FileInputStream fis = new FileInputStream(source);
				File targetDir = new File(PathKit.getWebRootPath()  + "/u/" + path);
				if (!targetDir.exists()) {
					targetDir.mkdirs();
				}
				File target = new File(targetDir, fileName);
				if (!target.exists()) {
					target.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(target);
				byte[] bts = new byte[300];
				while (fis.read(bts, 0, 300) != -1) {
					fos.write(bts, 0, 300);
				}
				fos.close();
				fis.close();
				source.delete();

				String templateFileName = PathKit.getWebRootPath()  + "/u/" + path +"/" +fileName;
				String sql = "insert into jms_demand (did,cusName,city,homeCounty,demandMemo,name,scene," +
						"submitDate,longitude,latitude,addr," +
						"radius,space,sysetemCnt,systemBand," +
						"aerialHeight,aerialCnt,isRRu,memo) " +
						"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				Map<String, Object> map = PoiExt.ReadExcel(templateFileName,"铁塔类需求收集表", sql,5,1,19);
				renderDWZSuccessJson("总记录数:"+map.get("count")+"；成功导入:"+map.get("success")+"；失败数"+map.get("lost"));

			} catch (FileNotFoundException e) {
				renderDWZErrorJson("上传出现错误，请稍后再上传");
			} catch (IOException e) {
			}catch (Exception e){
				renderDWZErrorJson("导入失败，请重新上传");
			}
		}

	}

	public void export() {

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		String name = getPara("demand.name");
		String cusName = getPara("demand.cusName");
		String memo = getPara("demand.memo");
		String demandMemo = getPara("demand.demandMemo");
		String code = getPara("demand.code");
		String crmid = getPara("demand.crmid");

		String homeCounty = getPara("demand.homeCounty");

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}

		if(ChristStringUtil.isNotEmpty(crmid)){
			whee.append(" and crmid like ?");
			param.add("%" + crmid + "%");
		}


		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and code like ?");
			param.add("%" + code + "%");
		}


		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty LIKE ?");
			param.add("%" + homeCounty + "%");
		}


		/**
		 * 清单
		 */
		String sql="select * from " + TABLE_Crm_Demand +" where 1=1 "+ whee.toString() ;

		List<Record> crmDetail = Db.find(sql, param.toArray());

		/**
		 * 区s 县汇总
		 */
		sql = "SELECT  date_format(now(),'%Y%m%d') sumDate,homeCounty,\n" +
				"demandCnt,screenCnt,confirmCnt,reachCnt,orderCnt,\n" +
				"round(orderCnt/demandCnt,2) orderRate,submitCnt,round(submitCnt/demandCnt,2) submitRate,\n" +
				"round(50*(orderCnt/demandCnt+submitCnt/demandCnt),2) score\n" +
				"from(\n" +
				"select a.homeCounty,count(*) cnt,\n" +
				"sum(case when b.requestStatus in(60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,10,20,21,30,31,32,40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) demandCnt,\n" +
				"sum(case when b.requestStatus in(65,66,67,68,69,70,71,72,73,74,10,20,21,30,31,32,40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) screenCnt,\n" +
				"sum(case when b.requestStatus in(69,70,71,72,73,74,10,20,21,30,31,32,40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) confirmCnt,\n" +
				"sum(case when b.requestStatus in(73,74,10,20,21,30,31,32,40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) reachCnt,\n" +
				"sum(case when b.requestStatus in(20,21,30,31,32,40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) orderCnt,\n" +
				"sum(case when b.requestStatus in(40,14,15,41,42,43,50,51,52,53) then 1 else 0 end) submitCnt\n" +
				" from crm_demand a\n" +
				"LEFT JOIN bas_demand b\n" +
				"on a.crmid = b.crmid\n" +
				"where 1=1  "+ whee.toString()+
				"GROUP BY a.homeCounty\n" +
				")t WHERE LENGTH(homeCounty)>0\n";

		List<Record> crmRecords = Db.find(sql,param.toArray());
		String sumDate = DateUtil.getStringDateShort();
		Db.update("delete a from crm_demand_sum a where sumDate = ?",sumDate);
		for(Record r:crmRecords){
			Db.save("crm_demand_sum",r);
		}
   		/*
   		1、需求转订单率
   		*/
		sql ="update crm_demand_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) sort from crm_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY orderRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.orderSort = b.sort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);

		/*
   		2、交付率
   		*/
		sql ="update crm_demand_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) sort from crm_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY submitRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.submitSort = b.sort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);


		/*
   		3、综合得分
   		*/
		sql ="update crm_demand_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) sort from crm_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY score desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.rank = b.sort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);

		sql="select * from crm_demand_sum a where sumDate = ?";
		List<Record> crmSum = Db.find(sql,sumDate);

		Map<String, Object> beans = new HashMap<String, Object>();
		if (StringUtil.isEmpty(cusName)) cusName="三家";
		beans.put("crmDetail", crmDetail);
		beans.put("crmSum", crmSum);
		beans.put("cusName",cusName);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_crm_demand.xls";
		String filename = "crm_demand.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));

	}

	public void transConfirm() {
		//需求确认
		String[] ids = getParaValues("ids");
		for(String id:ids) {
			Demand m = Demand.dao.findById(id);
			String isValid = m.getStr("isValid");
			if ( isValid.equals("否")){

				m.set("isValid", "是");
			}
			ModelUtils.fillForUpdate(m);
			m.update();
		}
		renderDWZSuccessJson("需求批量确认成功!");

	}


	@Before(AuthorizeInterceptor.class)
	public void delete() {

		String[] ids = getParaValues("ids");
		for(String id:ids){
			Demand.dao.deleteById(id);
		}
		renderDWZSuccessJson("删除成功!");

	}

	public void down(){

		String templateFileName = PathKit.getRootClassPath() + "/tpl_imp_demand.xls";

		renderFile(new File(templateFileName));
	}

}
